﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using jiaju.Areas.Back.Models.DBModel;
using jiaju.Areas.Back.Models;
using System.Data.SqlClient;
namespace jiaju.Areas.Back.Models.DAL
{
    public class MessageService
    {
        public static int UpdateStatus(string id,string Status)
        {
            string sql = string.Format("update Message set Status='{0}' where Id={1}", Status, id);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        public static int UpdateAnswer(string id, string Answer)
        {
            string sql = string.Format("update Message set Answer='{0}' where Id={1}", Answer, id);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        //获取表格并且分页
        public static List<Message> getMessage(int page, int limit, string name)
        {
            String sql = string.Format("select top({0}) Id,Name,Tel,Cont,Email,Status from dbo.Message where Id not in (select top(({1} - 1) * {0}) Id from Message)", limit, page);
            //通过sql查询数据
            if (!string.IsNullOrEmpty(name))
            {
                //如果title不为空，并且不为null，拼接上and Name like '%title%'
                sql = sql + string.Format("and Name like '%{0}%'", name);
            }
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            List<Message> list = new List<Message>();
            while (sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Name = (string)sdr["Name"];
                string Email = (string)sdr["Email"];
                string Tel = (string)sdr["Tel"];
                string Cont = (string)sdr["Cont"];
                string Status = (string)sdr["Status"];
                Message mes = new Message()
                {
                    Id = Id,
                    Name = Name,
                    Email=Email,
                    Tel=Tel,
                    Cont=Cont,
                    Status=Status
                };
                list.Add(mes);
            }
            return list;
        }
        public static int addMessage(Message message)
        {
            string sql = string.Format(@"INSERT INTO [dbo].[Message]
                               (Tel,Email,Name,Cont)
                         VALUES( '{0}', '{1}' ,'{2}','{3}')", message.Tel, message.Email, message.Name,message.Cont);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        //通过Id获取详情
        public static Message GetById(string ArtId)
        {
            string sql = "select * from dbo.Message where Id=" + ArtId;
            Message message = null;
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            while (sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Name = Convert.ToString(sdr["Name"]);
                string Email = Convert.ToString(sdr["Email"]);
                string Tel = Convert.ToString(sdr["Tel"]);
                string Cont = Convert.ToString(sdr["Cont"]);
                string Status = Convert.ToString(sdr["Status"]);
                string Answer = Convert.ToString(sdr["Answer"]);
                message = new Message()
                {
                    Id = Id,
                    Name = Name,
                    Email = Email,
                    Tel = Tel,
                    Cont = Cont,
                    Status=Status,
                    Answer=Answer
                };
            }
            return message;
        }
        //获取所有留言
        public static List<Message> getAllTitle()
        {
            string sql = "select Id,Name,Tel,Email,Cont from dbo.Message";
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            List<Message> list = new List<Message>();
            while (sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Name = (string)sdr["Name"];
                string Email = (string)sdr["Email"];
                string Tel = (string)sdr["Tel"];
                string Cont = (string)sdr["Cont"];
                Message mes = new Message() {
                    Id = Id,
                    Name = Name,
                    Email = Email,
                    Tel = Tel,
                    Cont = Cont
                };
                list.Add(mes);
            }
            return list;
        }
        //编辑
        public static int EditMessage(Message mes)
        {
            string sql = String.Format(@"UPDATE [dbo].[Message]
                                  SET Name ='{0}'
                                  ,Tel='{1}'
                                  ,Email='{2}'
                                  ,Cont='{3}'
                WHERE Id={4}", mes.Name,mes.Tel,mes.Email,mes.Cont,mes.Id);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        //删除
        public static int DelMessage(String Id)
        {
            String mes = "delete from dbo.Message where Id=" + Id;
            int num = DBHelp.ExecuteNonQuery(mes);
            return num;
        }
    }
}